﻿Imports System.Data.Common
Imports System.Data.Objects.DataClasses
Imports DotNetNuke.Modules.Announcements
Imports DotNetNuke.Modules.Announcements.Model
Imports System.Collections.Generic
Imports System.Linq
Imports Announcement = Model.Announcement

Namespace Components


    Public Class DataAccess
        Private Shared Function GetContext() As AnnouncementsEntities
            Dim dnnConnectStringBuilder As DbConnectionStringBuilder = DotNetNuke.Data.DataProvider.Instance().GetConnectionStringBuilder()
            dnnConnectStringBuilder.ConnectionString = DotNetNuke.Data.DataProvider.Instance().ConnectionString
            ' we need to check for MultipleActiveResultSets, which we need to have set to true
            If Not dnnConnectStringBuilder.ContainsKey("MultipleActiveResultSets") Then
                dnnConnectStringBuilder.Add("MultipleActiveResultSets", True)
            Else
                dnnConnectStringBuilder("MultipleActiveResultSets") = True
            End If

            Dim connectionString As String = String.Format("metadata=res://*/Model.AnnouncementsEntities.csdl|res://*/Model.AnnouncementsEntities.ssdl|res://*/Model.AnnouncementsEntities.msl;provider=System.Data.SqlClient;provider connection string=""{0}""", dnnConnectStringBuilder.ConnectionString)

            Return New AnnouncementsEntities(connectionString)
        End Function


        Public Shared Function Add(ByVal ModuleId As Integer, ByVal CreatedByUser As Integer, ByVal CreatedDate As Date?, ByVal Title As String, _
                                      ByVal imageSource As String, ByVal URL As String, ByVal Description As String, ByVal ViewOrder As Integer?, _
                                      ByVal PublishDate As DateTime?, ByVal ExpireDate As DateTime?) As Integer
            Using ctx As AnnouncementsEntities = GetContext()
                Dim newObject As Model.Announcement = New Model.Announcement() With { _
                    .DnnModuleReference = New EntityReference(Of [Module])() With {.EntityKey = New EntityKey("AnnouncementsEntities.Modules", "ModuleID", ModuleId)}, _
                    .CreatedByUser = CreatedByUser, _
                    .CreatedDate = If(CreatedDate = Null.NullDate, Nothing, CreatedDate.Value), _
                    .Title = Title, _
                    .ImageSource = imageSource, _
                    .URL = URL, _
                    .Description = Description, _
                    .ViewOrder = If(Not ViewOrder.HasValue OrElse ViewOrder = Null.NullInteger, Nothing, ViewOrder.Value), _
                    .PublishDate = If(PublishDate = Null.NullDate, Nothing, PublishDate.Value), _
                    .ExpireDate = If(ExpireDate = Null.NullDate, Nothing, ExpireDate.Value)}

                ctx.AddToAnnouncements(newObject)
                ctx.SaveChanges()
                Return newObject.ItemID
            End Using

            'insert into dbo.Announcements (
            '            ModuleId,
            '            CreatedByUser,
            '            CreatedDate,
            '            Title,
            '            imageSource,
            '            URL,
            '            Description,
            '            ViewOrder,
            '            PublishDate,
            '            ExpireDate
            ')
            'values (
            '	@ModuleId,
            '	@CreatedByUser,
            '	@CreatedDate,
            '	@Title,
            '	@ImageSource,
            '	@Url,
            '	@Description,
            '	@ViewOrder,
            '	@PublishDate,
            '	@ExpireDate
            ')
        End Function

        Public Shared Sub Delete(ByVal ModuleID As Integer, ByVal ItemId As Integer)
            Using ctx As AnnouncementsEntities = GetContext()
                Dim objectToDelete As Model.Announcement = [Get](ItemId, ModuleID, ctx)
                ctx.DeleteObject(objectToDelete)
                ctx.SaveChanges()
            End Using
        End Sub
        Public Shared Function [Get](ByVal ItemId As Integer, ByVal ModuleId As Integer) As Model.Announcement
            Using ctx As AnnouncementsEntities = GetContext()
                Return [Get](ItemId, ModuleId, ctx)
            End Using

        End Function

        Public Shared Function [Get](ByVal ItemId As Integer, ByVal ModuleId As Integer, ByVal ctx As AnnouncementsEntities) As Model.Announcement
            Return (From a As Model.Announcement In ctx.Announcements _
                    Where a.ItemID = ItemId AndAlso a.DnnModule.ModuleID = ModuleId _
                    Select a).FirstOrDefault()
        End Function

        Public Shared Function GetAll(ByVal ModuleId As Integer, ByVal StartDate As Date, ByVal EndDate As Date) As List(Of Model.Announcement)
            Using ctx As AnnouncementsEntities = GetContext()
                Return (From a As Model.Announcement In ctx.Announcements _
                        Where a.DnnModule.ModuleID = ModuleId _
                        AndAlso ((StartDate = Null.NullDate OrElse a.PublishDate >= StartDate) AndAlso (EndDate = Null.NullDate Or a.PublishDate <= EndDate)) _
                        Order By a.ViewOrder, a.PublishDate Descending _
                        Select a).ToList()
            End Using

            'select *
            'from   dbo.Announcements
            'left outer join dbo.UrlTracking on dbo.Announcements.URL = dbo.UrlTracking.Url and dbo.UrlTracking.ModuleId = @ModuleID 
            'where  dbo.Announcements.ModuleId = @ModuleId
            'and    ( ((dbo.Announcements.PublishDate >= @StartDate) or @StartDate is null) and ((dbo.Announcements.PublishDate <= @EndDate) or @EndDate is null) )
            'order by dbo.Announcements.ViewOrder asc, dbo.Announcements.PublishDate desc
        End Function

        Public Shared Function GetCurrent(ByVal ModuleId As Integer, ByVal StartDate As Date) As List(Of Model.Announcement)
            Using ctx As AnnouncementsEntities = GetContext()
                Dim q As IQueryable(Of Model.Announcement) = From a In ctx.Announcements _
                                       Where a.DnnModule.ModuleID = ModuleId _
                                       AndAlso (Not a.ExpireDate.HasValue OrElse a.ExpireDate.Value > DateTime.Now) _
                                       Order By a.ViewOrder, a.PublishDate Descending

                If StartDate <> Null.NullDate Then
                    q = q.Where(Function(a As Model.Announcement) (Not a.PublishDate.HasValue OrElse (a.PublishDate.Value <= DateTime.Now AndAlso a.PublishDate.Value >= StartDate)) _
                                    AndAlso (Not a.ExpireDate.HasValue OrElse a.ExpireDate.Value > DateTime.Now))
                End If
                '                       Select a
                'AndAlso ((StartDate = Null.NullDate OrElse a.PublishDate >= StartDate) AndAlso (a.PublishDate <= DateTime.Now)) _
                'AndAlso (Not a.ExpireDate.HasValue OrElse a.ExpireDate.Value > DateTime.Now) _

                Return q.ToList()
            End Using

            'select *
            'from   dbo.Announcements
            '       left outer join dbo.UrlTracking on dbo.Announcements.URL = dbo.UrlTracking.Url and dbo.UrlTracking.ModuleId = @ModuleID 
            'where  dbo.Announcements.ModuleId = @ModuleId
            '       and ( ((dbo.Announcements.PublishDate >= @StartDate) or @StartDate is null) and (dbo.Announcements.PublishDate <= GETDATE()) )
            '       and ( (dbo.Announcements.ExpireDate > getDate()) or (dbo.Announcements.ExpireDate is null) )
            'order by dbo.Announcements.ViewOrder asc, dbo.Announcements.PublishDate desc
        End Function

        Public Shared Function GetExpired(ByVal ModuleId As Integer) As List(Of Model.Announcement)
            Using ctx As AnnouncementsEntities = GetContext()
                Return (From a As Model.Announcement In ctx.Announcements _
                        Where a.DnnModule.ModuleID = ModuleId _
                        AndAlso a.ExpireDate.HasValue AndAlso a.ExpireDate.Value < DateTime.Now _
                        Order By a.ViewOrder, a.PublishDate Descending _
                        Select a).ToList()
            End Using

            'select *
            'from   dbo.Announcements
            '       left outer join dbo.UrlTracking on dbo.Announcements.URL = dbo.UrlTracking.Url and dbo.UrlTracking.ModuleId = @ModuleID 
            'where  dbo.Announcements.ModuleId = @ModuleId
            '       and dbo.Announcements.ExpireDate <=  getDate()
            'order by dbo.Announcements.ViewOrder asc, dbo.Announcements.PublishDate desc
        End Function


        Public Shared Sub Update(ByVal ItemId As Integer, ByVal ModuleID As Integer, ByVal CreatedByUser As Integer, ByVal CreatedDate As Date?, ByVal Title As String, ByVal imageSource As String, ByVal URL As String, ByVal Description As String, ByVal ViewOrder As Integer?, ByVal PublishDate As DateTime?, ByVal ExpireDate As DateTime?)
            Using ctx As AnnouncementsEntities = GetContext()
                Dim objectToUpdate As Model.Announcement = [Get](ItemId, ModuleID, ctx)
                With objectToUpdate
                    .CreatedByUser = CreatedByUser
                    .CreatedDate = If(CreatedDate = Null.NullDate, Nothing, CreatedDate.Value)
                    .Description = Description
                    .ExpireDate = If(ExpireDate = Null.NullDate, Nothing, ExpireDate.Value)
                    .ImageSource = imageSource
                    .PublishDate = If(PublishDate = Null.NullDate, Nothing, PublishDate.Value)
                    .Title = Title
                    .URL = URL
                    .ViewOrder = If(ViewOrder = Null.NullInteger, Nothing, ViewOrder.Value)
                End With
                ctx.SaveChanges()
            End Using
        End Sub
    End Class
End Namespace